package swu.commodity;

import swu.core.ObjectRepo;
import swu.core.ResultSetIterator;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class CommodityRepo extends ObjectRepo
{
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");

    private Commodity getOneCommodity(ResultSet resultSet) throws SQLException
    {
        Commodity commodity = new Commodity();

        commodity.setId(resultSet.getInt("id"));
        commodity.setName(resultSet.getString("name"));
        commodity.setOrigin(resultSet.getString("origin"));
        commodity.setPrice(resultSet.getDouble("price"));
        commodity.setCategory(resultSet.getString("category"));
        commodity.setProductionDate(resultSet.getDate("productionDate"));
        commodity.setImage(resultSet.getString("image"));

        return commodity;
    }
    
    public List<Commodity> getAll() throws SQLException
    {
        List<Commodity> commodities = new ArrayList<>();
        String sql = "select * from commodities";

        this.queryAll(sql, new ResultSetIterator() {
            @Override
            public void visit(ResultSet rs) throws SQLException
            {
                commodities.add(getOneCommodity(rs));
            }
        });

        return  commodities;
    }

    public void addCommodity(Commodity commodity) throws SQLException
    {
        String temp = "insert into commodities (name,origin,price,category,productionDate,image) values('%s','%s','%s','%s','%s','%s')";
        String sql = String.format(temp,commodity.getName(),commodity.getOrigin(),commodity.getPrice(),commodity.getCategory(),sdf.format(commodity.getProductionDate()),commodity.getImage());
        this.executeSQL(sql);
    }

    public void update(Commodity commodity) throws SQLException
    {
        String template = "UPDATE commodities SET name='%s', origin='%s', price='%s', category='%s', productionDate='%s', image='%s' WHERE id=%d";
        String sql = (String.format(template, commodity.getName(), commodity.getOrigin(), commodity.getPrice(),commodity.getCategory(), sdf.format(commodity.getProductionDate()), commodity.getImage(), commodity.getId()));
        this.executeSQL(sql);
    }

    public void deleteById(int id) throws SQLException
    {
        String template = "DELETE FROM commodities WHERE id = %d";
        String sql = String.format(template,id);
        this.executeSQL(sql);
    }

    public Commodity getById(int id) throws SQLException
    {
        List<Commodity> commodities = new ArrayList<>();
        String temp = "SELECT * FROM commodities WHERE id=%d";
        String sql = String.format(temp,id);
        this.queryAll(sql, new ResultSetIterator() {
            @Override
            public void visit(ResultSet rs) throws SQLException
            {
                commodities.add(getOneCommodity(rs));
            }
        });
        return commodities.size() > 0 ? commodities.get(0) : null;
    }

    public List<Commodity> search(String key) throws SQLException
    {
        List<Commodity> commodities = new ArrayList<>();
        String sql = "SELECT * FROM commodities WHERE name LIKE '%" + key + "%' " +
                "OR category LIKE '%" + key + "%'";
        this.queryAll(sql, new ResultSetIterator() {
            @Override
            public void visit(ResultSet rs) throws SQLException
            {
                commodities.add(getOneCommodity(rs));
            }
        });
        return commodities;
    }

    public void save(Commodity commodity) throws SQLException
    {
        if (commodity.getId() < 0)
        {
            this.addCommodity(commodity);
        }
        else
        {
            this.update(commodity);
        }
    }
}
